using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data;

namespace GenerationCode.Utils
{
	/// <summary>
	/// Summary description for Table.
	/// </summary>
	public class Table
	{
		#region fields		
		private string name;		
        private string connectionString;
		private string queryKey;
		private string queryColumn;
		private string queryIdentity;
        private string queryPK;
        
		private IList keyList;
        private IList lstColumn;
        private bool isCacheData;
        private bool isMultilanguage;
        private bool isSelect;//select for gencode
		#endregion

		#region constructor
		public Table ( string name, string connectionString ) 
		{
			this.name = name;
            this.connectionString = connectionString;
			this.queryKey = string.Format ( @"
									SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
									WHERE table_name = '{0}' AND (constraint_name like 'PK%'
										OR constraint_name like '%PK')", this.name );
			this.queryColumn = string.Format ( @"
									SELECT column_name, data_type, character_maximum_length
                                    FROM information_schema.columns
							        WHERE table_name = '{0}'", this.name );
			this.queryIdentity = string.Format ( @"
						SELECT INFORMATION_SCHEMA.COLUMNS.column_name,  
								(
										SELECT COLUMNPROPERTY(OBJECT_ID('{0}'),
								INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')
								) AS IsIdentity
						FROM           INFORMATION_SCHEMA.COLUMNS 
						WHERE  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='{0}'
						ORDER BY IsIdentity DESC
						", this.name );
            //this.queryPK = "SELECT column_name FROM information_schema.key_column_usage WHERE TABLE_NAME = '" + this.name + "'";
            this.queryPK = @"select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
                        inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
                         on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
                        where a.table_name = '" + this.name + "' and constraint_type = 'Primary key'";
           
			this.keyList = new ArrayList();
          //  this.lstColumn=GetColumns();
          
		}
		#endregion

		#region properties
		public string Name {
			get { 
				this.name = this.name.Replace( " ", "");
				return Utils.Utility.ConvertToPropertyName(this.name); 
			}
		}
		public string ConnectionString {
			get { 				
				return this.connectionString; 
			}
		}
		public IList Columns {
			get {
				return this.lstColumn;
                

			}
      set{ this.Columns = value;}
		}        
		public IList KeyList {
			get {
				return this.keyList;
			}
			set {
				this.keyList = value ;
			}
		}
        public bool IsCacheData
        {
            get { return this.isCacheData; }
            set { this.isCacheData = value; }
        }
        public bool IsMultilanguage
        {
            get { return this.isMultilanguage; }
            set { this.isMultilanguage = value; }
        }
        public bool IsSelect
        {
            get { return this.isSelect; }
            set { this.isSelect = value; }
        }
		#endregion

		#region methods
		public IList GetColumns () 
        {            
			IList lst = new ArrayList();
			IList keys = new ArrayList ();
            DataTable dtPK=this.GetPK();
            DataTable dtIdentity = this.GetIdentity();
			DataTable tb = null;
			try {
				this.keyList.Clear();
				keys = this.GetKeys();
				tb = Utility.GetObjects( this.connectionString, this.queryColumn );
				foreach ( DataRow row in tb.Rows )
                {
					string colName = (string) row[0];			
					string sqlType = (string) row[1];
					Type type = row[2].GetType();
					string length;
					if ( type.ToString() == "System.DBNull" )
						 length = "null";
					else 
						length = row[2].ToString();
                    //if (length == "-1" && sqlType == "varbinary")
                    //    length = "max";
                    if (length == "-1")
                        length = "max";
					//check identity
					bool isPK = false;
                    if (isColumnKey(dtPK, colName))
                        isPK = true;
                    bool isIdentity = false;
                    if (isIdentityColum(dtIdentity, colName))
                        isIdentity = true;
                    Column col = new Column ( colName, sqlType, length,isIdentity,isPK );					
					lst.Add( col );
					//check isKey
					if ( keys.Contains( ( string ) colName ) ) {
						this.keyList.Add ( col );
					}
				}				
			} catch ( Exception ex ) {
				throw ex;				
			}			
			this.lstColumn=lst;
            return lstColumn;
		}

        private bool isIdentityColum(DataTable dtSource, string col)
        {
            try
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    if (col.Trim().ToLower() == dtSource.Rows[i][0].ToString().Trim().ToLower() && dtSource.Rows[i][1].ToString()=="1")
                        return true;
                }
            }
            catch
            {

            }
            return false;
        }
		private IList GetKeys () {
			IList lst = new ArrayList();
			DataTable tb;
			try {
				tb = Utility.GetObjects( this.connectionString, this.queryKey );
				foreach ( DataRow row in tb.Rows ) {					
					lst.Add( row[0] );
				}				
			} catch ( Exception ex ) {
				throw ex;				
			}			
			return lst;		
		}
        private DataTable GetPK()
        {
            DataTable tb;
            try
            {
                tb = Utility.GetObjects(this.connectionString, this.queryPK);


            }
            catch (Exception ex)
            {
                throw ex;
            }
            return tb;
        }
		private DataTable GetIdentity () {
			string identity = string.Empty;
			DataTable tb;
			try
            {
                tb = Utility.GetObjects(this.connectionString, this.queryIdentity);	

			
			}
            catch( Exception ex )
            {
				throw ex;				
			}			
			return tb;	
		}
        private bool isColumnKey(DataTable dtSource, string col)
        {
            try
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    if (col.Trim().ToLower() == dtSource.Rows[i][0].ToString().Trim().ToLower())
                        return true;
                }
            }
            catch
            {
 
            }
            return false;
        }
		#endregion
	}
}
